package com.cupk.mapper;

import com.cupk.pojo.Record;
import com.cupk.pojo.Student;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.web.bind.annotation.PostMapping;

import java.util.List;
import java.util.Map;

@Mapper
public interface RecordMapper {
    @Insert("insert into record(stu_id,topic_id,answer,result,status,update_time) values (#{stuId},#{topicId},#{answer},#{result},#{status},now())")
    void addRecord(Record l);

    @Select("select record.*, student.name as stuName, student.stu_id as stuIdCard from record, student where record.stu_id = student.id and record.status = 0 " + "and record.topic_id in (select id from topic where stage_id = #{stageId}) and record.id = (select min(r.id) from record r where r.stu_id = record.stu_id and r.status = 0 " + "and r.topic_id IN (SELECT id FROM topic WHERE stage_id = #{stageId})) order by record.stu_id, record.topic_id")
    List<Record> getPreparingList(Integer stageId);

    @Select("select record.*, student.name as stuName, student.stu_id as stuIdCard from record, student where record.stu_id = student.id and record.status = 1 " + "and record.topic_id in (select id from topic where stage_id = #{stageId}) and record.id = (select min(r.id) from record r where r.stu_id = record.stu_id and r.status = 1 " + "and r.topic_id IN (SELECT id FROM topic WHERE stage_id = #{stageId})) order by record.stu_id, record.topic_id")
    List<Record> getFinishList(Integer stageId);

    @Select("select * from record where stu_id = #{stuId} and topic_id = #{topicId}")
    Record getRecordByStuAndTopic(Integer stuId, Integer topicId);

    @Update("update record set answer = #{answer}, result = #{result}, status = #{status} where stu_id = #{stuId} and topic_id = #{topicId}")
    void updateRecord(Record l);

    @Select("select * from record where id = #{id}")
    Record getRecordById(Integer id);

    @Update("update record set comment = #{comment}, status = 1 where stu_id = #{stuId} and topic_id in (select id from topic where stage_id = #{stageId})")
    void acceptRecord(Integer stageId, String comment, Integer stuId);

    @Update("update record set comment = #{comment}, status = 0 where stu_id = #{stuId} and topic_id in (select id from topic where stage_id = #{stageId})")
    void rejectRecord(Integer stageId, String comment, Integer stuId);

    @Select("select record.*,topic.question_text as topicName from record join topic on record.topic_id = topic.id where record.stu_id = #{userId} and topic.stage_id = #{stageId} order by record.stu_id,record.topic_id")
    List<Record> getRecordList(Integer stageId, Integer userId);

    @Update("update record set status = 1,comment = #{res} where id = #{id}")
    void acceptRecordByTransaction(Integer id, String res);

    @Select("select r.stu_id, s.stu_id as stu_id_card, r.answer, t.question_text, s.id, s.name, s.stu_id, s.identity_card, s.grade, s.college, s.major, s.classes, s.phone " + "FROM record r " + "JOIN topic t ON r.topic_id = t.id " + "JOIN student s ON r.stu_id = s.id " + "WHERE t.stage_id = #{stageId} and r.status = #{status}")
    List<Map<String, Object>> getAllInfoByStage(Integer stageId, Integer status);

    @Select("select DATE_FORMAT(update_time,'%Y-%m-%d %H:%i:00') as time,count(*) as score from record r " +
            "where r.topic_id in (select id from topic where stage_id in (select id from stage where match_id = #{matchId})) " +
            "group by time,stu_id order by time,stu_id")
    List<Map<String, Object>> divideMinute(Integer matchId);

    @Select("select s.major,s.grade,count(r.id) as record_count from record r join student s on r.stu_id = s.id where r.topic_id in (select id from topic where stage_id in (select id from stage where match_id = #{matchId})) group by s.major,s.grade order by s.major,s.grade")
    List<Map<String, Object>> divideGroupByMajorAndGrade(Integer matchId);

    @Select("select s.grade,count(r.id) as record_count from record r join student s on r.stu_id = s.id where r.topic_id in (select id from topic where stage_id in (select id from stage where match_id = #{matchId})) group by s.grade order by s.grade")
    List<Map<String, Object>> divideGroupByGrade(Integer matchId);


}
